/**
 * Copyright 2011, Under-Ground Studio.
 *  
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *  
 *      http://www.apache.org/licenses/LICENSE-2.0
 *  
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package cn.edu.zju.vlis.aus.db;

import java.util.ArrayList;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Set;

import android.content.ContentValues;
import android.content.Context;
import android.content.Intent;
import android.content.pm.PackageManager;
import android.content.pm.ResolveInfo;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteStatement;
import android.util.Log;
import cn.edu.zju.vlis.aus.bean.UsageRecord;
import cn.edu.zju.vlis.aus.util.AppHelper;

/**
 * Instance of this class is used for database access.
 * 
 * @author zll(zou.ziluolan@gmail.com)
 */
public class UsageRecordDAO {

	private static final String AUS_PACKAGE_NAME = "cn.edu.zju.vlis.aus";
	// database name and table name
	private static final String DATABASE_NAME = "AUS_DATABASE";
	private static final String TABLE_NAME_USAGE_RECORD = "USAGE_RECORD";
	// this table will only have one record at most.
	private static final String TABLE_NAME_USAGE_RECORD_DELETED = "USAGE_RECORD_LAST_DELETED";

	private static final int DATABASE_VERSION = 3;

	// The name of each column in your database
	public static final String KEY_PACKAGENAME = "package_name";
	public static final String KEY_USEDCOUNT = "used_count";
	public static final String KEY_USEDTIME = "used_time";
	public static final String KEY_LASTUSE = "last_use";
	public static final String KEY_APPNAME = "app_name";
	public static final String KEY_ISSTATISTIC = "is_statistic";

	// sql statements,the real type of KEY_USEDCOUNT&KEY_USEDTIME is int and
	// KEY_LASTUSE is Long;
	private static final String CREATE_TABLE_USAGE_RECORD = "CREATE TABLE "
			+ TABLE_NAME_USAGE_RECORD + " ( " + KEY_PACKAGENAME
			+ " VARCHAR(100) PRIMARY KEY NOT NULL , " + KEY_USEDCOUNT
			+ " INTEGER NOT NULL, " + KEY_USEDTIME + " INTEGER NOT NULL, "
			+ KEY_LASTUSE + " INTEGER NOT NULL, " + KEY_APPNAME
			+ " VARCHAR(50) NOT NULL, " + KEY_ISSTATISTIC
			+ " INTEGER NOT NULL); ";
	// sql statement for create the last delete table.
	private static final String CREATE_TABLE_USAGE_RECORD_DELETED = "CREATE TABLE "
			+ TABLE_NAME_USAGE_RECORD_DELETED
			+ " ( "
			+ KEY_PACKAGENAME
			+ " VARCHAR(100) PRIMARY KEY NOT NULL , "
			+ KEY_USEDCOUNT
			+ " INTEGER NOT NULL, "
			+ KEY_USEDTIME
			+ " INTEGER NOT NULL, "
			+ KEY_LASTUSE
			+ " INTEGER NOT NULL, "
			+ KEY_APPNAME
			+ " VARCHAR(50) NOT NULL, "
			+ KEY_ISSTATISTIC
			+ " INTEGER NOT NULL); ";

	private static final String DROP_TABLE_USAGE_RECORD = "DROP TABLE IF EXISTS "
			+ TABLE_NAME_USAGE_RECORD;

	private static final String DROP_TABLE_USAGE_RECORD_DELETED = "DROP TABLE IF EXISTS "
			+ TABLE_NAME_USAGE_RECORD_DELETED;

	private Context context;
	private SQLiteDatabase db;
	private static Object dbLock = new Object();

	public UsageRecordDAO(Context context) {
		this.context = context;
		db = context.openOrCreateDatabase(DATABASE_NAME, 0, null);
	}

	public boolean isCreated() {
		return db.getVersion() == DATABASE_VERSION;
	}

	/**
	 * create and init the database. [This method could take some time to
	 * finish!]
	 */
	public void createAndInitTable() {
		synchronized (dbLock) {
			db.beginTransaction();
			try {
				db.execSQL(DROP_TABLE_USAGE_RECORD);
				db.execSQL(DROP_TABLE_USAGE_RECORD_DELETED);
				db.execSQL(CREATE_TABLE_USAGE_RECORD);
				db.execSQL(CREATE_TABLE_USAGE_RECORD_DELETED);
				// init usage table
				StringBuffer sqlBuilder = new StringBuffer("INSERT INTO "
						+ TABLE_NAME_USAGE_RECORD);
				sqlBuilder.append("(").append(KEY_PACKAGENAME).append(",");
				sqlBuilder.append(KEY_USEDCOUNT).append(",");
				sqlBuilder.append(KEY_USEDTIME).append(",");
				sqlBuilder.append(KEY_LASTUSE).append(",");
				sqlBuilder.append(KEY_ISSTATISTIC).append(",");
				sqlBuilder.append(KEY_APPNAME).append(")");
				sqlBuilder.append("VALUES (?,?,?,?,?,?)");
				SQLiteStatement stmt = db.compileStatement(sqlBuilder
						.toString());
				PackageManager pm = context.getPackageManager();
				Intent intent = new Intent(Intent.ACTION_MAIN, null);
				intent.addCategory(Intent.CATEGORY_LAUNCHER);
				List<ResolveInfo> resolveInfoList = pm.queryIntentActivities(
						intent, PackageManager.PERMISSION_GRANTED);// PackageManager.GET_ACTIVITIES
				// eliminate the duplicate package name
				Iterator<ResolveInfo> it = resolveInfoList.iterator();
				Set<String> packageNameSet = new HashSet<String>();
				while (it.hasNext()) {
					ResolveInfo info = it.next();
					if (!packageNameSet.add(info.activityInfo.packageName)) {
						it.remove();
					}
				}
				it = resolveInfoList.iterator();
				while (it.hasNext()) {
					ResolveInfo info = it.next();
					String packageName = info.activityInfo.packageName;
					// do not insert ourself into database.
					if (AUS_PACKAGE_NAME.equals(packageName)) {
						continue;
					}
					String appName = (String) info.activityInfo.applicationInfo
							.loadLabel(pm);
					stmt.bindString(1, packageName);
					stmt.bindLong(2, 0);
					stmt.bindLong(3, 0);
					stmt.bindLong(4, 0);
					stmt.bindLong(5, 1);
					stmt.bindString(6, appName);
					stmt.executeInsert();
				}
				db.setVersion(DATABASE_VERSION);
				db.setTransactionSuccessful();
			} finally {
				db.endTransaction();
			}
		}
	}

	/**
	 * Insert a record into the table.
	 * 
	 * @param r
	 */
	public void insert(UsageRecord r) {
		// do not insert ourself into database.
		if (AUS_PACKAGE_NAME.equals(r.getPackageName())) {
			return;
		}
		synchronized (dbLock) {
			db.beginTransaction();
			try {
				insert(r, false);
				db.setTransactionSuccessful();
			} finally {
				db.endTransaction();
			}
		}
	}

	private void insert(UsageRecord r, boolean toDeletedTable) {
		String tableName = TABLE_NAME_USAGE_RECORD;
		if (toDeletedTable) {
			tableName = TABLE_NAME_USAGE_RECORD_DELETED;
			db.delete(tableName, null, null);
		} else {
			String appName = AppHelper.getAppName(context, r.getPackageName());
			r.setAppName(appName);
			r.setStatistic(true);
		}
		ContentValues initialValues = new ContentValues();
		initialValues.put(KEY_PACKAGENAME, r.getPackageName());
		initialValues.put(KEY_APPNAME, r.getAppName());
		initialValues.put(KEY_USEDCOUNT, r.getUseCount());
		initialValues.put(KEY_USEDTIME, r.getUseTime());
		initialValues.put(KEY_LASTUSE, r.getLastUse());
		initialValues.put(KEY_ISSTATISTIC, r.isStatistic());
		long ret = db.insert(tableName, null, initialValues);
		if (ret == -1) {
			Log.e("UsageRecordDAO", "Error when insert record to table "
					+ tableName);
		}
	}

	/**
	 * delete a record with the given appName and store the record to the
	 * deleted table for possibly later use.
	 * 
	 * @param packageName
	 */
	public void delete(String packageName) {
		synchronized (dbLock) {
			db.beginTransaction();
			try {
				UsageRecord rec = select(packageName, false);
				if (rec != null) {
					db.delete(TABLE_NAME_USAGE_RECORD, KEY_PACKAGENAME + " = '"
							+ packageName + "'", null);
					insert(rec, true);
				}
				db.setTransactionSuccessful();
			} finally {
				db.endTransaction();
			}
		}
	}

	/**
	 * select record whose appName equals the param appName
	 * 
	 * @param packageName
	 *            used as the whereclause
	 * @return the query result or null if no result . As the appName is the
	 *         primary key ,there is always only one fit record , so the return
	 *         type is UsageRecord instead of List<UsageRecord>
	 * 
	 */
	private UsageRecord select(String packageName, boolean fromDeletedTable) {
		UsageRecord rec = null;
		String where = KEY_PACKAGENAME + " = '" + packageName + "'";
		String tableName = TABLE_NAME_USAGE_RECORD;
		if (fromDeletedTable) {
			tableName = TABLE_NAME_USAGE_RECORD_DELETED;
		}
		Cursor cur = db.query(tableName, null, where, null, null, null, null);
		if (cur != null && cur.moveToFirst()) {
			rec = new UsageRecord();
			rec.setPackageName(cur.getString(0));
			rec.setUseCount(cur.getInt(1));
			rec.setUseTime(cur.getInt(2));
			rec.setLastUse(cur.getLong(3));
			rec.setAppName(cur.getString(4));
			rec.setStatistic(cur.getInt(5) == 1);
		}
		cur.close();
		return rec;
	}

	/**
	 * Read the deleted table to see is there a same record in the table, if
	 * true, merge that record to usage table.
	 * 
	 * @param packageName
	 */
	public void mergeForPackageReplace(String packageName) {
		synchronized (dbLock) {
			db.beginTransaction();
			try {
				UsageRecord rec = select(packageName, true);
				if (rec != null) {
					mergeSingleRecord(rec);
				}
				db.setTransactionSuccessful();
			} finally {
				db.endTransaction();
			}
		}
	}

	/**
	 * update the UseCount,UseTime,LastUse of the record whose appName equals to
	 * r.getPackageName() with the value r
	 * 
	 * @param r
	 *            the new value
	 */
	private void update(UsageRecord r) {
		ContentValues newValues = new ContentValues();
		newValues.put(KEY_USEDCOUNT, r.getUseCount());
		newValues.put(KEY_USEDTIME, r.getUseTime());
		newValues.put(KEY_LASTUSE, r.getLastUse());
		String whereClause = KEY_PACKAGENAME + " = '" + r.getPackageName()
				+ "'";
		db.update(TABLE_NAME_USAGE_RECORD, newValues, whereClause, null);
	}

	/**
	 * merge the given record with the record in database
	 * 
	 * @param r
	 *            the given UsageRecord to be merged
	 */
	public void merge(List<UsageRecord> recList) {
		synchronized (dbLock) {
			db.beginTransaction();
			try {
				if (recList != null && !recList.isEmpty()) {
					for (UsageRecord r : recList) {
						mergeSingleRecord(r);
					}
				}
				db.setTransactionSuccessful();
			} finally {
				db.endTransaction();
			}
		}
	}

	private void mergeSingleRecord(UsageRecord r) {
		UsageRecord old = select(r.getPackageName(), false);
		if (old == null) {
			Log.w("UsageRecordDAO",
					"Can not merged record ["
							+ r
							+ "] into the database due to no same package name exists in database");
		} else {
			old.setUseCount(old.getUseCount() + r.getUseCount());
			old.setUseTime(old.getUseTime() + r.getUseTime());
			long oldLastUse = old.getLastUse();
			long rLastUse = r.getLastUse();
			if (oldLastUse < rLastUse) {
				old.setLastUse(rLastUse);
			}
			update(old);
		}
	}

	/**
	 * update the is_statistic value of the UsageRecord in the list
	 * 
	 * @param list
	 *            all the UsageRecord in the list has to be updated with the
	 *            is_statistic
	 */
	public void updateStatistic(ArrayList<UsageRecord> list) {
		synchronized (dbLock) {
			db.beginTransaction();
			try {
				for (UsageRecord r : list) {
					ContentValues newValues = new ContentValues();
					newValues.put(KEY_ISSTATISTIC, r.isStatistic() ? 1 : 0);
					String whereClause = KEY_PACKAGENAME + " = '"
							+ r.getPackageName() + "'";
					db.update(TABLE_NAME_USAGE_RECORD, newValues, whereClause,
							null);
				}
				db.setTransactionSuccessful();
			} finally {
				db.endTransaction();
			}
		}
	}

	/**
	 * get all the app with appName,packageName and isStatistic order by app
	 * name
	 * 
	 * @return the list of all the UsageRecord
	 */
	public ArrayList<UsageRecord> getAllStatisticByAppName() {
		ArrayList<UsageRecord> list = new ArrayList<UsageRecord>();
		Cursor cur = db.query(TABLE_NAME_USAGE_RECORD, null, null, null, null,
				null, KEY_APPNAME + " COLLATE LOCALIZED ASC");
		if (cur != null) {
			while (cur.moveToNext()) {
				UsageRecord ur = new UsageRecord();
				ur.setPackageName(cur.getString(0));
				ur.setAppName(cur.getString(4));
				ur.setStatistic(cur.getInt(5) == 1);
				list.add(ur);
			}
		}
		cur.close();
		return list;
	}

	/**
	 * get all the app with appName,packageName and isStatistic order by use
	 * time
	 * 
	 * @return the list of all the UsageRecord
	 */
	public ArrayList<UsageRecord> getAllStatistic() {
		ArrayList<UsageRecord> list = new ArrayList<UsageRecord>();
		Cursor cur = db.query(TABLE_NAME_USAGE_RECORD, null, null, null, null,
				null, null);
		if (cur != null) {
			while (cur.moveToNext()) {
				UsageRecord ur = new UsageRecord();
				ur.setPackageName(cur.getString(0));
				ur.setAppName(cur.getString(4));
				ur.setStatistic(cur.getInt(5) == 1);
				list.add(ur);
			}
		}
		cur.close();
		return list;
	}

	// get the used rank app list,ranking according to used_time
	public ArrayList<UsageRecord> getRankListByTime() {
		ArrayList<UsageRecord> listMost = new ArrayList<UsageRecord>();
		// "select * from UR WHERE used_count>0 ORDER BY used_time DESC;"
		Cursor cur = db.query(TABLE_NAME_USAGE_RECORD, null, KEY_USEDTIME
				+ ">0 AND " + KEY_ISSTATISTIC + " = 1", null, null, null,
				KEY_USEDTIME + " DESC", null);
		if (cur != null) {
			while (cur.moveToNext()) {
				String packageName = cur.getString(0);
				int useCount = cur.getInt(1);
				int useTime = cur.getInt(2);
				long lastUse = cur.getLong(3);
				String appName = cur.getString(4);
				UsageRecord ur = new UsageRecord(packageName, useCount,
						useTime, lastUse);
				ur.setAppName(appName);
				listMost.add(ur);
			}
			cur.close();
		}
		return listMost;
	}

	public ArrayList<UsageRecord> getRankListByCount() {
		ArrayList<UsageRecord> listMost = new ArrayList<UsageRecord>();
		// "select * from UR WHERE used_count>0 ORDER BY used_count DESC;"
		Cursor cur = db.query(TABLE_NAME_USAGE_RECORD, null, KEY_USEDCOUNT
				+ ">0 AND " + KEY_ISSTATISTIC + " = 1", null, null, null,
				KEY_USEDCOUNT + " DESC", null);
		if (cur != null) {
			while (cur.moveToNext()) {
				String packageName = cur.getString(0);
				int useCount = cur.getInt(1);
				int useTime = cur.getInt(2);
				long lastUse = cur.getLong(3);
				String appName = cur.getString(4);
				UsageRecord ur = new UsageRecord(packageName, useCount,
						useTime, lastUse);
				ur.setAppName(appName);
				listMost.add(ur);
			}
			cur.close();
		}
		return listMost;
	}

	public ArrayList<UsageRecord> getRankListByLastUse() {
		ArrayList<UsageRecord> listMost = new ArrayList<UsageRecord>();
		// "select * from UR WHERE used_count>0 ORDER BY used_count DESC;"
		Cursor cur = db.query(TABLE_NAME_USAGE_RECORD, null, KEY_USEDCOUNT
				+ ">0 AND " + KEY_ISSTATISTIC + " = 1", null, null, null,
				KEY_LASTUSE + " DESC", null);
		if (cur != null) {
			while (cur.moveToNext()) {
				String packageName = cur.getString(0);
				int useCount = cur.getInt(1);
				int useTime = cur.getInt(2);
				long lastUse = cur.getLong(3);
				String appName = cur.getString(4);
				UsageRecord ur = new UsageRecord(packageName, useCount,
						useTime, lastUse);
				ur.setAppName(appName);
				listMost.add(ur);
			}
			cur.close();
		}
		return listMost;
	}

	public ArrayList<UsageRecord> getUnusedList() {
		ArrayList<UsageRecord> listLeast = new ArrayList<UsageRecord>();
		// "select * from UR WHERE used_count=0 ORDER BY app_name ASC;"
		String where = KEY_USEDCOUNT + "=0 AND " + KEY_ISSTATISTIC + " = 1";
		Cursor cur = db.query(TABLE_NAME_USAGE_RECORD, null, where, null, null,
				null, KEY_APPNAME + "  COLLATE LOCALIZED ASC", null);
		if (cur != null) {
			while (cur.moveToNext()) {
				String packageName = cur.getString(0);
				int useCount = cur.getInt(1);
				int useTime = cur.getInt(2);
				long lastUse = cur.getLong(3);
				String appName = cur.getString(4);
				UsageRecord ur = new UsageRecord(packageName, useCount,
						useTime, lastUse);
				ur.setAppName(appName);
				listLeast.add(ur);
			}
			cur.close();
		}
		return listLeast;
	}

	/**
	 * close the data base.
	 */
	public void close() {
		if (db != null && db.isOpen()) {
			db.close();
		}
	}

}
